﻿using BySoft.Server.Common;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsBOMChangeDataAccess
    {

        public static DataTable SelectList(string BOMChangeID, string ProductName, string cpFrom, string cpEnd, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_BOMChange o where 1=1 ";
                    if (!string.IsNullOrEmpty(BOMChangeID))
                    {
                        strcmd += " and o.BOMChangeID like @BOMChangeID";
                        dpc.AddWithValue("@BOMChangeID", "%" + BOMChangeID + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and o.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                    }
                    if (!string.IsNullOrEmpty(cpFrom))
                    {
                        strcmd += " and o.BillDate >= @From";
                        dpc.AddWithValue("@From", cpFrom + " 00:00:00");
                    }
                    if (!string.IsNullOrEmpty(cpEnd))
                    {
                        strcmd += " and o.BillDate <= @End";
                        dpc.AddWithValue("@End", cpFrom + " 23:59:59");
                    }
                    strcmd += " order by o.BillDate desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogProduct(string BOMChangeID, string ProductID, string ProductName, string userid, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select b.*,p.ProductName,p.Unit,p.Specifications from BS_BOM b
                                        left join MD_Product p on p.ProductID=b.ProductID
                                        where 1=1";
                    if (!string.IsNullOrEmpty(BOMChangeID))
                    {
                        strcmd += "  and b.BOMID not in (select BOMID from BS_BOMChange where BOMChangeID<>@BOMChangeID)";
                        dpc.AddWithValue("@BOMChangeID", BOMChangeID);
                    }
                    if (!string.IsNullOrEmpty(ProductID))
                    {
                        strcmd += " and b.ProductID like @ProductID";
                        dpc.AddWithValue("@ProductID", "%" + ProductID + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and p.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                    }
                    strcmd += " order by b.ProductID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogMaterial(string BOMID, string MaterialID, string MaterialName, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select bd.*,m.MaterialName,m.Specifications,m.Unit 
                                        from BS_BOMDetail bd
                                        left join MD_Material m on m.MaterialID=bd.MaterialID
                                        where 1=1";
                    if (!string.IsNullOrEmpty(BOMID))
                    {
                        strcmd += " and bd.BOMID = @BOMID";
                        dpc.AddWithValue("@BOMID", BOMID);
                    }
                    if (!string.IsNullOrEmpty(MaterialID))
                    {
                        strcmd += " and bd.MaterialID like @MaterialID";
                        dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                    }
                    if (!string.IsNullOrEmpty(MaterialName))
                    {
                        strcmd += " and m.MaterialName like @MaterialName";
                        dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
                    }
                    strcmd += " order by bd.MaterialID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetDataById(string id)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_BOMChange where BOMChangeID=@BOMChangeID";
                    dpc.AddWithValue("@BOMChangeID", id);
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static bool UpdateBomChangeNum(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessParameterCollection dpc=new DataAccessParameterCollection();
                    string str = @"select * from V_BOMChange where BOMChangeID=@BOMChangeID";
                    dpc.AddWithValue("@BOMChangeID",strCode);
                    DataTable dt = DataAccessCommon.Select(str, dpc).Tables[0];
                    string strs = "";
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            decimal nums = CommonMethod.ConvertToDecimal(dr["Nums"]);
                            if (!string.IsNullOrEmpty(CommonMethod.GetString(dr["ChangeType"])) && CommonMethod.GetString(dr["ChangeType"]).Equals("2")) 
                            {
                                nums = nums * -1;
                            }
                            strs += @" update BS_BOMDetail set ChangeNum=ISNULL(ChangeNum,0)+" + nums + " where BOMDetailID=" + CommonMethod.GetString(dr["BOMDetailID"]) + ";";
                        }
                    }
                    broker.ExecuteSQL(strs);
                    ClsCommDA.UpdateStatus("BS_BOMChange", "BOMChangeID", strCode, broker);
                    string strcc = "update BS_BOMChangeDetail set IsCheck='1' where BOMChangeID='" + strCode + "'";
                    broker.ExecuteSQL(strcc);
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        }
    }
}
